
import java.sql.Statement;
import java.sql.Connection;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class DB {
    //private static String DB_NAME="e://crossword.mdb";
    
    public static Connection getConnection() throws Exception {
        String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + System.getProperty("user.dir")+"\\src\\crossword.mdb";
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Properties props = new Properties();
        props.put("charSet", "UTF8");
        return DriverManager.getConnection(url,props);
    }
    static int InsertQ(int width,int height,String title)
    {
        try {
            Connection conn = getConnection();
            Statement st=conn.createStatement();
            st.execute("insert into tblEx(width,height,title)values("+width+","+height+",'"+title+"')");
            ResultSet rs=st.executeQuery("select top 1 ExID from tblEx order by exid desc");
            rs.next();
            return rs.getInt(1);
        } catch (Exception ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }
    static int InsertQ(String time,int width,int height,String title)
    {
        try {
            Connection conn = getConnection();
            Statement st=conn.createStatement();
            st.execute("insert into tblEx(width,height,title,time)values("+width+","+height+",'"+title+"'"+time+")");
            ResultSet rs=st.executeQuery("select top 1 ExID from tblEx order by exid desc");
            rs.next();
            return rs.getInt(1);
        } catch (Exception ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }
    static void InsertDetailQ(int ID,String title,String answer,int point)
    {
        try {
            Connection conn = getConnection();
            Statement st=conn.createStatement();
            st.execute("insert into tblQuestion(QuestionID,title,answer,point)values("+ID+",'"+title+"','"+answer+"',"+point+")");
        } catch (Exception ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    static ResultSet GetInfoGame(String ID,int type)
    {
        ResultSet rs=null;
        try {
            Connection conn = getConnection();
            Statement st=conn.createStatement();
            if(type==0)
                rs=st.executeQuery("select * from tblex where exid="+ID);
            else if(type==1)
                rs=st.executeQuery("select * from tblQuestion where questionID="+ID+" order by point");
        } catch (Exception ex) { 
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
    static ResultSet GetHS(String type)
    {
        ResultSet rs=null;
        try {
            Connection conn = getConnection();
            Statement st=conn.createStatement();
            rs=st.executeQuery("select * from tblHighScore where type='"+type+"' order by mark desc");
        } catch (Exception ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
    static void SetHS(String name,int mark,String date, String time,String type)
    {
        try {
            Connection conn = getConnection();
            Statement st=conn.createStatement();
            st.execute("insert into tblHighScore(username,mark,_date,_time,type) values('"+name+"',"+mark+",'"+date+"','"+time+"','"+type+"')");
            st.execute("delete * from tblhighScore where type='"+type+"' and id not in(select top 10 id from tblhighscore where type='"+type+"' order by mark desc)");
        } catch (Exception ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    static ResultSet GetType()
    {
        ResultSet rs=null;
        try {
            Connection conn = getConnection();
            Statement st=conn.createStatement();
            rs=st.executeQuery("select width,height from tblex group by width,height");
        } catch (Exception ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
    static ResultSet GetQByType(String width,String height)
    {
        ResultSet rs=null;
        try {
            Connection conn = getConnection();
            Statement st=conn.createStatement();
            rs=st.executeQuery("select * from tblEx where width="+width+" and height="+height);
        } catch (Exception ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
}
